using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class Testing_ImportBundooraAgencies2007 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        DataSourceSelectArguments dssa = new DataSourceSelectArguments();

        dssa.AddSupportedCapabilities(DataSourceCapabilities.RetrieveTotalRowCount);
        dssa.RetrieveTotalRowCount = true;

        DataTable dt = ((DataView)this.SqlDataSource1.Select(dssa)).Table;



        PlacementManager.VenueComponent oVenueC = new PlacementManager.VenueComponent();
        PlacementManager.AddressComponent oAddressC = new PlacementManager.AddressComponent();
        PlacementManager.LinkVenueAddressComponent oLvaC = new PlacementManager.LinkVenueAddressComponent();
        PlacementManager.Geo_AdministrativeDivisionComponent oGeoAdmin = new PlacementManager.Geo_AdministrativeDivisionComponent();
        PlacementManager.GoogleMap oGM = new PlacementManager.GoogleMap();

        StringBuilder sbMissingAgencies = new StringBuilder();
        int iMissing = 0;
        //Let's do the agencies and their addresses first
        foreach (DataRow dr in dt.Rows)
        {
            string agency = dr["Agency"].ToString();

            //try and find a match
            List<PlacementManager.Venue> oVenueList = oVenueC.SelectAllActiveStartingWith(agency, "VenueName");
            if (oVenueList.Count > 0)
            {
                string stateName = "VICTORIA";
                int stateId = 2;
                List<PlacementManager.Geo_AdministrativeDivision> oStateList = oGeoAdmin.SelectAllWDivisionPostalAbbreviation(dr["State"].ToString(),"DivisionName");
                if(oStateList.Count>0)
                {
                    stateName = oStateList[0].DivisionName;
                    stateId = oStateList[0].AdministrativeDivisionID;
                }

                string address = dr["Address"].ToString() + ", ";
                address += dr["Suburb"].ToString() + ", " + stateName + ", AUSTRALIA";

                float[] coordinates = oGM.GetLongitudeLatitude(address);
                
                
                //Get the venue object
                PlacementManager.Venue oVenue = oVenueList[0];
                oVenue.GenericEmail = dr["Email"].ToString();
                oVenue.TelephoneNumber = dr["Phone"].ToString();
                oVenueC.Update(oVenue);

                //Delete current links with address
                oLvaC.DeleteAllWVenueId(oVenue.VenueId);

                //Add an address
                PlacementManager.Address oAddress = new PlacementManager.Address();
                oAddress.Address1 = string.Empty;
                oAddress.Address2 = string.Empty;
                oAddress.Street = dr["Address"].ToString();
                oAddress.City = dr["Suburb"].ToString();
                oAddress.StateId = stateId;
                oAddress.CountryId = 14;
                oAddress.CreatedBy = "msmithers";
                oAddress.DateCreated = DateTime.Now;
                oAddress.DateUpdated = DateTime.Now;
                oAddress.IsActive = true;
                oAddress.IsPostal = true;
                oAddress.Latitude = coordinates[0];
                oAddress.Longitude = coordinates[1];
                //oAddress.Latitude = 0;
                //oAddress.Longitude = 0;
                oAddress.PostCode = dr["Code"].ToString();
                oAddress.UpdatedBy = "msmithers";

                int addressId = oAddressC.Insert(oAddress);

                PlacementManager.LinkVenueAddress oLva = new PlacementManager.LinkVenueAddress();
                oLva.AddressId = addressId;
                oLva.CreatedBy = "msmithers";
                oLva.DateCreated = DateTime.Now;
                oLva.DateUpdated = DateTime.Now;
                oLva.IsActive = true;
                oLva.IsPrimary = true;
                oLva.UpdatedBy = "msmithers";
                oLva.VenueId = oVenue.VenueId;

                oLvaC.Insert(oLva);
            }
            else
            {
                sbMissingAgencies.Append("<br />" + agency);
                iMissing++;
            }
        }

        this.Label1.Text = iMissing.ToString() + "Missing:<br />" + sbMissingAgencies.ToString();
    }
    protected void Contacts_Click(object sender, EventArgs e)
    {
        DataSourceSelectArguments dssa = new DataSourceSelectArguments();

        dssa.AddSupportedCapabilities(DataSourceCapabilities.RetrieveTotalRowCount);
        dssa.RetrieveTotalRowCount = true;

        PlacementManager.ContactComponent oContactC = new PlacementManager.ContactComponent();
        PlacementManager.VenueComponent oVenueC = new PlacementManager.VenueComponent();
        PlacementManager.LinkVenueContactComponent oLvcC = new PlacementManager.LinkVenueContactComponent();

        DataTable dt = ((DataView)this.SqlDataSource1.Select(dssa)).Table;
        StringBuilder sbMissingAgencies = new StringBuilder();
        int contactAdded = 0;
        int contactLinksAdded = 0;
        //Let's do the contacts
        foreach (DataRow dr in dt.Rows)
        {
            string firstName = dr["1st Name"].ToString();
            string lastName = dr["2nd Name"].ToString();
            string position = dr["Position"].ToString();

            List<PlacementManager.Contact> oContactList = oContactC.SelectAllWFirstNameLastName(firstName, lastName, "LastName");
            int contactId = 0;
            if (oContactList.Count == 1) //we've found an existing contact
            {
                contactId = oContactList[0].ContactId;
            }

            if (oContactList.Count == 0)//There is no contact with this name
            {
                if (lastName.Length > 0 && firstName.Length > 0)
                {
                    PlacementManager.Contact oContact = new PlacementManager.Contact();
                    oContact.CreatedBy = "msmithers";
                    oContact.DateCreated = DateTime.Now;
                    oContact.DateUpdated = DateTime.Now;
                    oContact.DivisionId = 1;
                    oContact.EducatorCurrentRate = 0;
                    oContact.EducatorTypeId = 1;
                    oContact.EmailAddress = string.Empty;
                    oContact.FirstName = firstName;
                    oContact.IsActive = true;
                    oContact.IsAgency = false;
                    oContact.IsEducator = false;
                    oContact.JobTitle = position;
                    oContact.LastName = lastName;
                    oContact.UpdatedBy = "msmithers";

                    contactId = oContactC.Insert(oContact);
                    contactAdded++;
                }
            }
            //Let's try and match the venue
            List<PlacementManager.Venue> oVenueList = oVenueC.SelectAllActiveStartingWith(dr["Agency"].ToString(), "VenueName");
            if (oVenueList.Count > 0)
            {
                int venueId = oVenueList[0].VenueId;

                //OK so we have a match for venue and contact, let's see if the relationship exists
                List<PlacementManager.LinkVenueContact> oLvcList = oLvcC.SelectAllWVenueIdContactId(venueId, contactId, "ContactId");
                if (oLvcList.Count == 0 && contactId > 0)//No relationship
                {
                    PlacementManager.LinkVenueContact oLvc = new PlacementManager.LinkVenueContact();
                    oLvc.ContactId = contactId;
                    oLvc.CreatedBy = "msmithers";
                    oLvc.DateCreated = DateTime.Now;
                    oLvc.DateUpdated = DateTime.Now;
                    oLvc.IsActive = true;
                    oLvc.IsPrimary = true;
                    oLvc.UpdatedBy = "msmithers";
                    oLvc.VenueId = venueId;
                    oLvcC.Insert(oLvc);
                    contactLinksAdded++;
                }
            }   
        }
        this.Label1.Text = "contacts added: " + contactAdded.ToString() + " linksadded: " + contactLinksAdded.ToString();
    }
    protected void Button1_Click1(object sender, EventArgs e)
    {
        DataSourceSelectArguments dssa = new DataSourceSelectArguments();

        dssa.AddSupportedCapabilities(DataSourceCapabilities.RetrieveTotalRowCount);
        dssa.RetrieveTotalRowCount = true;

        DataTable dt = ((DataView)this.SqlDataSource1.Select(dssa)).Table;

        PlacementManager.VenueComponent oVenueC = new PlacementManager.VenueComponent();
        PlacementManager.LinkVenueUnitComponent oLvuC = new PlacementManager.LinkVenueUnitComponent();
        PlacementManager.UnitComponent oUnitC = new PlacementManager.UnitComponent();

        StringBuilder sbMissingAgencies = new StringBuilder();
        int iAddedUnits = 0;
        //Let's do the agencies and their units

       

        foreach (DataRow dr in dt.Rows)
        {
            string agency = dr["Agency"].ToString();
            //try and find a match
            List<PlacementManager.Venue> oVenueList = oVenueC.SelectAllActiveStartingWith(agency, "VenueName");
            if (oVenueList.Count > 0)
            {
                //Get the venue object
                PlacementManager.Venue oVenue = oVenueList[0];
                

                string unitCode = (dr["Unit"].ToString()).Trim();

                int unitId = oUnitC.SelectOneWUnitCode(unitCode).UnitId;

                if (unitId > 0)
                {
                    PlacementManager.LinkVenueUnit oLvu = new PlacementManager.LinkVenueUnit();
                    oLvu.CreatedBy = "msmithers";
                    oLvu.DateCreated = DateTime.Now;
                    oLvu.DateUpdated = DateTime.Now;
                    oLvu.IsActive = true;
                    oLvu.UnitId = unitId;
                    oLvu.CampusId = 1;
                    oLvu.UpdatedBy = "msmithers";
                    oLvu.VenueId = oVenue.VenueId;
                    oLvuC.Insert(oLvu);

                    iAddedUnits++;
                }
            }
            else
            {
                sbMissingAgencies.Append("<br />" + agency);
            }
        }

        this.Label1.Text = "Units added:" + iAddedUnits.ToString() + " Missing Venues: " + sbMissingAgencies.ToString();
    }
}
